using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;
namespace WPFExcelReport
{
    public partial class VAPARTE
    {
        public void SetUpNewRow(ref DataRow dwReturn)
        {
            #region
            #endregion
            switch (app.company)
            {
                case "YGR":
                case "SXN":
                case "TST":
                case "UBT":
                default:
                    #region
                    dwReturn["APAEMANDANT"] = "00";
                    dwReturn["APAEWERK"] = "   ";
                    dwReturn["APAEANRKRS"] = "GY";
                    dwReturn["APAEALFDNR"] = "000021";
                    dwReturn["APAEAFOLGE"] = "050";
                    dwReturn["APAEAEVOR"] = "0001";
                    dwReturn["APAEMODNR"] = "010";
                    dwReturn["APAEEINSTNR"] = "010";
                    dwReturn["APAEWERTX"] = "100             ";
                    dwReturn["APAEWERTN"] = "0000000000000000";
                    dwReturn["APAEPROGNR"] = "0000";
                    dwReturn["APAEEINAUS"] = " ";
                    dwReturn["APAEREZEPTKZ"] = " ";
                    dwReturn["APAEREZEPTNR"] = "          ";
                    dwReturn["APAEREZEPTST"] = "0";
                    dwReturn["APAEWERKZ"] = "              ";
                    dwReturn["APAEFAKTOR"] = "00";
                    dwReturn["APAESTAKZ"] = " ";
                    dwReturn["APAEMODEIN"] = " ";
                    dwReturn["APAEAENDZL"] = "0";
                    dwReturn["APAEAENDBEN"] = "        ";
                    dwReturn["APAEAENDDAT"] = "0";
                    dwReturn["APAENEUBEN"] = "WANGW   ";
                    dwReturn["APAENEUDAT"] = "20140704";
                    dwReturn["APAEFILLER"] = "                                 ";
                    #endregion
                    break;
            }
        }

        public void InsertAPARTE(DataRow dwData, string ITXDBVERSION = "50.24")
        {

            or1 = OracleDB.CreateCommand();
            switch (app.company)
            {
                case "YGR":
                case "SXN":
                case "TST":
                case "UBT":
                default:
                    or1.CommandText = "INSERT INTO APARTE (APAEMANDANT,APAEWERK,APAEANRKRS,APAEALFDNR,APAEAFOLGE,APAEAEVOR,APAEMODNR,APAEEINSTNR,APAEWERTX,APAEWERTN,APAEPROGNR,APAEEINAUS,APAEREZEPTKZ,APAEREZEPTNR,APAEREZEPTST,APAEWERKZ,APAEFAKTOR,APAESTAKZ,APAEMODEIN,APAEAENDZL,APAEAENDBEN,APAEAENDDAT,APAENEUBEN,APAENEUDAT,APAEFILLER)"
                    + " VALUES (:APAEMANDANT:,:APAEWERK:,:APAEANRKRS:,:APAEALFDNR:,:APAEAFOLGE:,:APAEAEVOR:,:APAEMODNR:,:APAEEINSTNR:,:APAEWERTX:,:APAEWERTN:,:APAEPROGNR:,:APAEEINAUS:,:APAEREZEPTKZ:,:APAEREZEPTNR:,:APAEREZEPTST:,:APAEWERKZ:,:APAEFAKTOR:,:APAESTAKZ:,:APAEMODEIN:,:APAEAENDZL:,:APAEAENDBEN:,:APAEAENDDAT:,:APAENEUBEN:,:APAENEUDAT:,:APAEFILLER:)";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
            }
            Connect2Proxy(or1);
        }
        public DataSet QueryAPARTE(DataRow dwData, int option = 1)
        {

            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 0: SResult = QKSZ00(dwData); break;
                case 1: SResult = QKSZ01(dwData); break;
                case 2: SResult = QKSZ02(dwData); break;
                default:
                    break;
            }

            return SResult;
        }
        public DataSet QKSZ00(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM APARTE WHERE  ROWNUM<2 ";
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QKSZ01(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM APARTE WHERE  "
                        + "  APAEMANDANT=:APAEMANDANT:  AND APAEANRKRS=:APAEANRKRS: AND APAEALFDNR=:APAEALFDNR: ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QKSZ02(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT DISTINCT AMEMESCHL,AMEKBEZ, M.* FROM APARTE M  "
                + " JOIN APARTK K ON APAKMANDANT=APAEMANDANT AND APAKANRKRS=APAEANRKRS AND APAKALFDNR=APAEALFDNR AND APAKAFOLGE=APAEAFOLGE AND APAKAEVOR=APAEAEVOR"
                + " JOIN AMSTAMM ON AMMANDANT = APAKMANDANT AND APAKPKSTKRS = AMKSTKRS AND APAKPKST = AMKST AND AMTYP = APAKMTYP AND AMSTDVAR = 'J'"
                + " JOIN AMEINST ON AMEMANDANT = AMMANDANT AND AMENRKRS = AMNRKRS AND AMLFDNR = AMELFDNR AND AMEPOS = AMPOS  AND AMEMODNR = APAEMODNR AND AMEEINSTNR = APAEEINSTNR"
                + "  WHERE APAEMANDANT=:APAEMANDANT:  AND APAEANRKRS=:APAEANRKRS: AND APAEALFDNR=:APAEALFDNR: AND APAEAFOLGE=:APAEAFOLGE: AND APAEAEVOR=:APAEAEVOR:  "
                + "  ORDER BY 2,3,4,5,6,7,8,9,1 ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        } 
        public DataSet QKSZ03(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT DISTINCT APAEAEVOR AS KEY, APAEAEVOR AS DES,AMEKBEZ, AMEMESCHL,M.* FROM  APARTE M"
                  + " JOIN APARTK K ON APAKMANDANT=APAEMANDANT AND APAKANRKRS=APAEANRKRS AND APAKALFDNR=APAEALFDNR AND APAKAFOLGE=APAEAFOLGE AND APAKAEVOR=APAEAEVOR"
                + " JOIN AMSTAMM ON AMMANDANT = APAKMANDANT AND APAKPKSTKRS = AMKSTKRS AND APAKPKST = AMKST AND AMTYP = APAKMTYP AND AMSTDVAR = 'J'"
                + " JOIN AMEINST ON AMEMANDANT = AMMANDANT AND AMENRKRS = AMNRKRS AND AMLFDNR = AMELFDNR AND AMEPOS = AMPOS  AND AMEMODNR = APAEMODNR AND AMEEINSTNR = APAEEINSTNR"
            + " WHERE APAEMANDANT=:APAEMANDANT:"
            + dwData["APAEANRKRS"].ToString()
                + dwData["APAEALFDNR"].ToString()
                + dwData["APAEAFOLGE"].ToString()
                + dwData["APAEAEVOR"].ToString()
                + dwData["APAEMODNR"].ToString()
                + dwData["APAEEINSTNR"].ToString();
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QKSZ04(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM APARTE    "
                        + " WHERE  APAEMANDANT=:APAEMANDANT: AND APAEANRKRS=:APAEANRKRS: AND APAEALFDNR=:APAEALFDNR: AND APAEAFOLGE=:APAEAFOLGE: AND APAEAEVOR=:APAEAEVOR: ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public void DARE00(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "DELETE FROM  APARTE"
              + " WHERE APAEMANDANT=:APAEMANDANT: ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);

        }
        public void DARE01(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "DELETE FROM  APARTE "
            + " WHERE APAEMANDANT=:APAEMANDANT: AND APAEANRKRS=:APAEANRKRS: AND APAEALFDNR=:APAEALFDNR: AND APAEAFOLGE=:APAEAFOLGE: AND APAEAEVOR=:APAEAEVOR: AND APAEMODNR=:APAEMODNR: AND APAEEINSTNR=:APAEEINSTNR:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);

        }
        public void DARE02(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "DELETE FROM  APARTE"
            + " WHERE APAEMANDANT=:APAEMANDANT: AND APAEANRKRS=:APAEANRKRS: AND APAEALFDNR=:APAEALFDNR: AND APAEAFOLGE=:APAEAFOLGE: AND APAEAEVOR=:APAEAEVOR:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
        }
        public void DARE03(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "DELETE FROM  APARTE"
            + " WHERE APAEMANDANT=:APAEMANDANT: AND APAEANRKRS=:APAEANRKRS: AND APAEALFDNR=:APAEALFDNR: AND APAEAFOLGE=:APAEAFOLGE: ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
        }
        public void DARE04(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "DELETE FROM  APARTE"
            + " WHERE APAEMANDANT=:APAEMANDANT: AND APAEANRKRS=:APAEANRKRS: AND APAEALFDNR=:APAEALFDNR: ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
        }

        public void UpdateAPARTE(DataRow dwData, int option = 1)
        {
            if (option == 0)
            {
                switch (app.company)
                {
                    case "YGR":
                    case "SXN":
                    case "TST":
                    case "UBT":
                    default:
                        UKSZ01(dwData); break;
                }
            }

        }

        public void UKSZ01(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "UPDATE APARTE SET APAEMANDANT=:APAEMANDANT:,APAEWERK=:APAEWERK:,APAEANRKRS=:APAEANRKRS:,APAEALFDNR=:APAEALFDNR:,APAEAFOLGE=:APAEAFOLGE:,APAEAEVOR=:APAEAEVOR:,APAEMODNR=:APAEMODNR:,APAEEINSTNR=:APAEEINSTNR:,APAEWERTX=:APAEWERTX:,APAEWERTN=:APAEWERTN:,APAEPROGNR=:APAEPROGNR:,APAEEINAUS=:APAEEINAUS:,APAEREZEPTKZ=:APAEREZEPTKZ:,APAEREZEPTNR=:APAEREZEPTNR:,APAEREZEPTST=:APAEREZEPTST:,APAEWERKZ=:APAEWERKZ:,APAEFAKTOR=:APAEFAKTOR:,APAESTAKZ=:APAESTAKZ:,APAEMODEIN=:APAEMODEIN:,APAEAENDZL=:APAEAENDZL:,APAEAENDBEN=:APAEAENDBEN:,APAEAENDDAT=:APAEAENDDAT:,APAENEUBEN=:APAENEUBEN:,APAENEUDAT=:APAENEUDAT:,APAEFILLER=:APAEFILLER:"
            + " WHERE APAEMANDANT=:APAEMANDANT: AND APAEANRKRS=:APAEANRKRS: AND APAEALFDNR=:APAEALFDNR: AND APAEAFOLGE=:APAEAFOLGE: AND APAEAEVOR=:APAEAEVOR: AND APAEMODNR=:APAEMODNR: AND APAEEINSTNR=:APAEEINSTNR:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);

        }
    }
}
